﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using RD.Metadata.Database.Sql;

using log4net;

namespace AvvaMobileNHibernateHelperKit.Framework
{
    public class Data
    {
        #region log4net
        protected static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        #endregion
    	
        protected static SqlConnection conn = new SqlConnection();
        protected static SqlMetaDatabase metainfo ;

        protected SqlCommand cmd = new SqlCommand();

        protected Dictionary<string, SqlMetaTable> tableMetaData = new Dictionary<string, SqlMetaTable>();

        #region Public Properties

        public string ConnectionString
        {
            get { return conn.ConnectionString; }
            set { 
            	conn.ConnectionString = value;
            	this.Load();
            }
        }

        #endregion
        
        
        #region Public Methods
        
        public void Load()
        {
        	// call this method everytime the connection string is set
        	metainfo = new SqlMetaDatabase("System.Data.SqlClient", conn.ConnectionString);
        	metainfo.Load();
        	
        	// populate the dictionary of tables.
        	foreach(SqlMetaTable table in metainfo.Tables) {
        		tableMetaData[ table.TableName ] = table;
        	}
        	
        }

        public DataTable ExecuteReader(SqlCommand command)
        {
            try
            {
                using (command)
                {
                    //Assing the connection to command object.
                    command.Connection = conn;

                    //command.CommandType = CommandType.StoredProcedure;

                    //Create a data table to filloutn and return.
                    DataTable dt = new DataTable();

                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    //Open connection.
                    conn.Open();

                    //Create a pointer for datareader and assing to command execute reader.
                    SqlDataReader SqlDataReader = command.ExecuteReader();

                    //Create a pointer for the datacolumn.
                    DataColumn dc;

                    //Get columns from database then apply them to datatable.
                    for (int i = 0; i < SqlDataReader.FieldCount; i++)
                    {
                        dc = new DataColumn();
                        dc.ColumnName = SqlDataReader.GetName(i);
                        dc.DataType = SqlDataReader.GetFieldType(i);
                        dt.Columns.Add(dc);
                    }

                    //Create a pointer for the datarow.
                    DataRow dr;

                    //Read data.
                    while (SqlDataReader.Read())
                    {
                        dr = dt.NewRow();
                        for (int j = 0; j < SqlDataReader.FieldCount; j++)
                        {
                            dr[j] = SqlDataReader[j];
                        }
                        dt.Rows.Add(dr);
                    }

                    //Close datareader.
                    SqlDataReader.Close();

                    //Return Datatable with data.
                    return dt;
                }
            }
            finally
            {
                //Close connection.
                conn.Close();
            }
        }

        public void ExecuteNonQuery(SqlCommand command)
        {
            try
            {
                //Assing the connection to command object.
                command.Connection = conn;

                //Using sql command.
                using (command)
                {
                    command.CommandType = CommandType.StoredProcedure;
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    //Open connection.
                    conn.Open();

                    //Run command.
                    command.ExecuteNonQuery();


                }
            }
            finally
            {
                //Close connection.
                conn.Close();
            }

        }

        #endregion


        public DataTable GetDatabaseNames(string commandString)
        {
            cmd.CommandText = commandString;
            return ExecuteReader(cmd);
        }

        public void TestConnection()
        {
            conn.Open();
            conn.Close();
        }

        public DataTable ExecuteReader(string commandString)
        {
            cmd.CommandText = commandString;
            return ExecuteReader(cmd);
        }

        public DataTable GetTableColumnsInfo(string commandString)
        {
            SqlDataAdapter adp = new SqlDataAdapter(commandString, conn);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            return dt;
        }
        
        public SqlMetaTable GetTableInfo(string table)
        {
        	if (tableMetaData.ContainsKey( table ) ) {
        		return tableMetaData[ table ];
        	} else {
        		log.Debug("** Unable to find meta data for table="+table);
        		log.Debug("** Tables with meta include: ");
        		
        		foreach (string tableName in tableMetaData.Keys) {
        			log.Debug("   - "+tableName);
        		}
        	}
        	
        	return null;
        }
        
        /*
        public DataTable GetSchemaInfo(SchemaCollection collection, string entity)
        {
        	DataTable dt = null;
        	string collectionName = "";
        	string[] restrictions = null;
        	try {
        		conn.Open();
        		switch (collection)
        		{
        			case SchemaCollection.Databases:
        				collectionName="Databases";
        				if (!String.IsNullOrEmpty(entity)) {
        					restrictions = new string[1];
        					restrictions[0] = entity;
        				}
        				break;
        			case SchemaCollection.Tables:
        				collectionName="Tables";
        				if (!String.IsNullOrEmpty(entity)) {
        					restrictions = new string[4];
        					restrictions[0] = entity;
        				}
        				break;
        			case SchemaCollection.Columns:
        				collectionName="Columns";
        				if (!String.IsNullOrEmpty(entity)) {
        					restrictions = new string[4];
        					restrictions[2] = entity;
        				}
        				break;
        		}
        		
        		dt = conn.GetSchema(collectionName, restrictions);
        	} finally {
        		conn.Close();
        	}
        	
        	return dt;
        }
        */
        
    }
}
